package com.practicesuite.storage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author Dan
 */
public class MyQuery {

    
    private String driver = "com.mysql.jdbc.Driver";
    private String urlPrefix = "jdbc:mysql://184.106.203.95:3306/";
    private String dbUsername = "ron";
    private String dbPassword = "ronaldbrown";
    private String dbName = "ron";


    private Connection connect = null;

    public String getDbPassword() {
        return dbPassword;
    }

    public void setDbPassword(String dbPassword) {
        this.dbPassword = dbPassword;
    }

    public String getDbUsername() {
        return dbUsername;
    }

    public void setDbUsername(String dbUsername) {
        this.dbUsername = dbUsername;
    }



    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }


    /**
     * Creates connection to Database
     * @return Returns connection
     */
    public Connection getConnection() {
        try {
            String url = urlPrefix + dbName;
            //Class.forName(driver).newInstance();
            connect = DriverManager.getConnection(
                    url, dbUsername, dbPassword);
        } catch (Exception lex) {
            lex.printStackTrace();
        }
        return connect;
    }

    /**
     * Runs execute Statement based off query
     * @param query The string to be executed
     * @return Returns RecordSet
     */
    public ResultSet executeQueryStatement(String query) {
        ResultSet rs = null;
        connect = getConnection();
        try {
            Statement stmt = connect.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(query);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return rs;
    }

    /**
     * Closes connection to DB
     */
    public void closeConnection() {
        if (connect != null) {
            try {
                connect.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * Package method to execute query with parameter
     * @param sqlQuery Query statement
     * @param where Array of parameters saved as strings
     * @return Returns ResultSet of data
     */
    public ResultSet executeQueryWhere(String sqlQuery, String[] where) {
        ResultSet rs = null;
         connect = getConnection();
         try {
             PreparedStatement ps = connect.prepareStatement(sqlQuery);
             for (int i = 0; i < where.length; i++) {
                 ps.setString((i+1), where[i]);
             }
             rs = ps.executeQuery();
         } catch (Exception ex) {
             ex.printStackTrace();
         }
         return rs;

    }
    
    public void executeUpdateWhere(String sqlQuery, String[] where) {
        ResultSet rs = null;
         connect = getConnection();
         try {
             PreparedStatement ps = connect.prepareStatement(sqlQuery);
             for (int i = 0; i < where.length; i++) {
                 ps.setString((i+1), where[i]);
             }
              ps.execute();
         } catch (Exception ex) {
             ex.printStackTrace();
         }
         

    }
    
    /**
     * Added by Ron to aid SystemDriver and finding patients based off their name
     * @param name Name to be searched
     * @return Returns ResultSet based off query
     */
    public ResultSet executeLookupPatientByName(String name) {
        String[] param = {name};
        
        return executeQueryWhere("Select * from Patient where name=",param);
    }
   
   

}
